{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 工具包"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#coding=utf-8"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-26T05:34:54.624167Z",
     "start_time": "2017-09-26T05:34:46.420964Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import time\n",
    "\n",
    "#Python SQLITE数据库是一款非常小巧的嵌入式开源数据库软件\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 载入原始数据"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get more information about the Millionsong project from https://labrosa.ee.columbia.edu/millionsong/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load Triplets data  [user, song, play_count]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Get the data from http://labrosa.ee.columbia.edu/millionsong/sites/default/files/challenge/train_triplets.txt.zip"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T09:48:06.127761Z",
     "start_time": "2017-09-24T09:48:05.969929Z"
    }
   },
   "outputs": [],
   "source": [
    "#数据集太大，读10000条记录看看\n",
    "triplet_dataset = pd.read_csv(filepath_or_buffer = 'train_triplets.txt', \n",
    "                              nrows=10000,sep='\\t', header=None, \n",
    "                              names=['user','song','play_count'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T09:48:29.300568Z",
     "start_time": "2017-09-24T09:48:29.275986Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user</th>\n",
       "      <th>song</th>\n",
       "      <th>play_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOAKIMP12A8C130995</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOAPDEY12A81C210A9</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOBBMDR12A8C13253B</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOBFNSP12AF72A0E22</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOBFOVM12A58A7D494</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOBNZDC12A6D4FC103</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOBSUJE12A6D4F8CF5</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOBVFZR12A6D4F8AE3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOBXALG12A8C13C108</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOBXHDL12A81C204C0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       user                song  play_count\n",
       "0  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOAKIMP12A8C130995           1\n",
       "1  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOAPDEY12A81C210A9           1\n",
       "2  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOBBMDR12A8C13253B           2\n",
       "3  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOBFNSP12AF72A0E22           1\n",
       "4  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOBFOVM12A58A7D494           1\n",
       "5  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOBNZDC12A6D4FC103           1\n",
       "6  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOBSUJE12A6D4F8CF5           2\n",
       "7  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOBVFZR12A6D4F8AE3           1\n",
       "8  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOBXALG12A8C13C108           1\n",
       "9  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOBXHDL12A81C204C0           1"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "triplet_dataset.head(n=10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 计算每个用户的总播放次数\n",
    "看哪些用户最活跃（play counts最多）\n",
    "由于空间有限，不活跃的用户不考虑了（共有约1M用户）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T10:00:40.238849Z",
     "start_time": "2017-09-24T09:57:54.875213Z"
    }
   },
   "outputs": [],
   "source": [
    "output_dict = {} #user及对应的play_count次数集合(在所有的歌曲上求和)\n",
    "with open('train_triplets.txt') as f:\n",
    "    for line_number, line in enumerate(f):\n",
    "        user = line.split('\\t')[0]            #第一列为用户id\n",
    "        play_count = int(line.split('\\t')[2]) #第三列为播放次数\n",
    "        if user in output_dict:\n",
    "            play_count +=output_dict[user]\n",
    "            output_dict.update({user:play_count})\n",
    "        output_dict.update({user:play_count})\n",
    "output_list = [{'user':k,'play_count':v} for k,v in output_dict.items()]\n",
    "play_count_df = pd.DataFrame(output_list)\n",
    "\n",
    "#按总播放次数排序\n",
    "play_count_df = play_count_df.sort_values(by = 'play_count', ascending = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "play_count_df.to_csv(path_or_buf='user_playcount_df.csv', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>play_count</th>\n",
       "      <th>user</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>669980</th>\n",
       "      <td>13132</td>\n",
       "      <td>093cb74eb3c517c5179ae24caf0ebec51b24d2a2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>402687</th>\n",
       "      <td>9884</td>\n",
       "      <td>119b7c88d58d0c6eb051365c103da5caf817bea6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>964856</th>\n",
       "      <td>8210</td>\n",
       "      <td>3fa44653315697f42410a30cb766a4eb102080bb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>462404</th>\n",
       "      <td>7015</td>\n",
       "      <td>a2679496cd0af9779a92a13ff7c6af5c81ea8c7b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>991089</th>\n",
       "      <td>6494</td>\n",
       "      <td>d7d2d888ae04d16e994d6964214a1de81392ee04</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        play_count                                      user\n",
       "669980       13132  093cb74eb3c517c5179ae24caf0ebec51b24d2a2\n",
       "402687        9884  119b7c88d58d0c6eb051365c103da5caf817bea6\n",
       "964856        8210  3fa44653315697f42410a30cb766a4eb102080bb\n",
       "462404        7015  a2679496cd0af9779a92a13ff7c6af5c81ea8c7b\n",
       "991089        6494  d7d2d888ae04d16e994d6964214a1de81392ee04"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "play_count_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 计算每首歌曲的总播放次数\n",
    "看哪些歌曲被播放次数最多（play counts最多）\n",
    "由于空间有限，不受欢迎的歌曲先不考虑（共有约1M用户）\n",
    "\n",
    "其实两个统计次数一起做亦可（如果机器能存储两个字典）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T10:08:50.652416Z",
     "start_time": "2017-09-24T10:05:53.721519Z"
    }
   },
   "outputs": [],
   "source": [
    "output_dict = {}\n",
    "with open('train_triplets.txt') as f:\n",
    "    for line_number, line in enumerate(f):\n",
    "        song = line.split('\\t')[1]            #第二列为歌曲id\n",
    "        play_count = int(line.split('\\t')[2]) #第三列为播放次数\n",
    "        if song in output_dict:\n",
    "            play_count +=output_dict[song]\n",
    "            output_dict.update({song:play_count})\n",
    "        output_dict.update({song:play_count})\n",
    "output_list = [{'song':k,'play_count':v} for k,v in output_dict.items()]\n",
    "song_count_df = pd.DataFrame(output_list)\n",
    "\n",
    "#按总播放次数排序\n",
    "song_count_df = song_count_df.sort_values(by = 'play_count', ascending = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "song_count_df.to_csv(path_or_buf='song_playcount_df.csv', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 最活跃的用户和最流行的歌曲"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T10:08:54.987662Z",
     "start_time": "2017-09-24T10:08:53.518248Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>play_count</th>\n",
       "      <th>user</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13132</td>\n",
       "      <td>093cb74eb3c517c5179ae24caf0ebec51b24d2a2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9884</td>\n",
       "      <td>119b7c88d58d0c6eb051365c103da5caf817bea6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8210</td>\n",
       "      <td>3fa44653315697f42410a30cb766a4eb102080bb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7015</td>\n",
       "      <td>a2679496cd0af9779a92a13ff7c6af5c81ea8c7b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6494</td>\n",
       "      <td>d7d2d888ae04d16e994d6964214a1de81392ee04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6472</td>\n",
       "      <td>4ae01afa8f2430ea0704d502bc7b57fb52164882</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6150</td>\n",
       "      <td>b7c24f770be6b802805ac0e2106624a517643c17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5656</td>\n",
       "      <td>113255a012b2affeab62607563d03fbdf31b08e7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>5620</td>\n",
       "      <td>6d625c6557df84b60d90426c0116138b617b9449</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5602</td>\n",
       "      <td>99ac3d883681e21ea68071019dba828ce76fe94d</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   play_count                                      user\n",
       "0       13132  093cb74eb3c517c5179ae24caf0ebec51b24d2a2\n",
       "1        9884  119b7c88d58d0c6eb051365c103da5caf817bea6\n",
       "2        8210  3fa44653315697f42410a30cb766a4eb102080bb\n",
       "3        7015  a2679496cd0af9779a92a13ff7c6af5c81ea8c7b\n",
       "4        6494  d7d2d888ae04d16e994d6964214a1de81392ee04\n",
       "5        6472  4ae01afa8f2430ea0704d502bc7b57fb52164882\n",
       "6        6150  b7c24f770be6b802805ac0e2106624a517643c17\n",
       "7        5656  113255a012b2affeab62607563d03fbdf31b08e7\n",
       "8        5620  6d625c6557df84b60d90426c0116138b617b9449\n",
       "9        5602  99ac3d883681e21ea68071019dba828ce76fe94d"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "play_count_df = pd.read_csv(filepath_or_buffer='user_playcount_df.csv')\n",
    "play_count_df.head(n =10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T10:09:06.800300Z",
     "start_time": "2017-09-24T10:09:06.427869Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>play_count</th>\n",
       "      <th>song</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>726885</td>\n",
       "      <td>SOBONKR12A58A7A7E0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>648239</td>\n",
       "      <td>SOAUWYT12A81C206F1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>527893</td>\n",
       "      <td>SOSXLTC12AF72A7F54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>425463</td>\n",
       "      <td>SOFRQTD12A81C233C0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>389880</td>\n",
       "      <td>SOEGIYH12A6D4FC0E3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>356533</td>\n",
       "      <td>SOAXGDH12A8C13F8A1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>292642</td>\n",
       "      <td>SONYKOW12AB01849C9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>274627</td>\n",
       "      <td>SOPUCYA12A8C13A694</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>268353</td>\n",
       "      <td>SOUFTBI12AB0183F65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>244730</td>\n",
       "      <td>SOVDSJC12A58A7A271</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   play_count                song\n",
       "0      726885  SOBONKR12A58A7A7E0\n",
       "1      648239  SOAUWYT12A81C206F1\n",
       "2      527893  SOSXLTC12AF72A7F54\n",
       "3      425463  SOFRQTD12A81C233C0\n",
       "4      389880  SOEGIYH12A6D4FC0E3\n",
       "5      356533  SOAXGDH12A8C13F8A1\n",
       "6      292642  SONYKOW12AB01849C9\n",
       "7      274627  SOPUCYA12A8C13A694\n",
       "8      268353  SOUFTBI12AB0183F65\n",
       "9      244730  SOVDSJC12A58A7A271"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "song_count_df = pd.read_csv(filepath_or_buffer='song_playcount_df.csv')\n",
    "song_count_df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 从总体数据中抽取子集\n",
    "前10万个用户\n",
    "前10万个（10%）用户的播放次数和占总播放次数的40%（2/8原则：20%的用户播放了80%的次数）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T10:16:05.763243Z",
     "start_time": "2017-09-24T10:16:05.700186Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "40.8807280500655"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_play_count = sum(song_count_df.play_count)\n",
    "(float(play_count_df.head(n=100000).play_count.sum())/total_play_count)*100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "play_count_subset = play_count_df.head(n=100000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T10:26:37.537061Z",
     "start_time": "2017-09-24T10:26:37.528055Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "23.679207859478584"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#取3万首歌曲（10%）\n",
    "#前3万首歌曲的播放次数占总播放次数的80%\n",
    "(float(song_count_df.head(n=800).play_count.sum())/total_play_count)*100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T10:26:38.311410Z",
     "start_time": "2017-09-24T10:26:38.306426Z"
    }
   },
   "outputs": [],
   "source": [
    "song_count_subset = song_count_df.head(n=800)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T10:26:39.337554Z",
     "start_time": "2017-09-24T10:26:39.326529Z"
    }
   },
   "outputs": [],
   "source": [
    "user_subset = list(play_count_subset.user)\n",
    "song_subset = list(song_count_subset.song)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T10:30:23.563673Z",
     "start_time": "2017-09-24T10:27:22.711377Z"
    }
   },
   "outputs": [],
   "source": [
    "triplet_dataset = pd.read_csv(filepath_or_buffer = 'train_triplets.txt',sep='\\t', \n",
    "                              header=None, names=['user','song','play_count'])\n",
    "triplet_dataset_sub = triplet_dataset[triplet_dataset.user.isin(user_subset) ]\n",
    "del(triplet_dataset)\n",
    "triplet_dataset_sub_song = triplet_dataset_sub[triplet_dataset_sub.song.isin(song_subset)]\n",
    "del(triplet_dataset_sub)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "triplet_dataset_sub_song.to_csv(path_or_buf ='triplet_dataset_sub_song.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T10:32:35.248710Z",
     "start_time": "2017-09-24T10:32:35.241709Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2297978, 3)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "triplet_dataset_sub_song.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "即使这样，还有1千万条记录"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T10:33:25.958644Z",
     "start_time": "2017-09-24T10:33:25.939631Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user</th>\n",
       "      <th>song</th>\n",
       "      <th>play_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>498</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOADQPP12A67020C82</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOAFTRR12AF72A8D4D</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>500</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOANQFY12AB0183239</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>501</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOAYATB12A6701FD50</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>502</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOBOAFP12A8C131F36</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>503</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOBONKR12A58A7A7E0</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SODASIJ12A6D4F5D89</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>507</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SODEAWL12AB0187032</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>508</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SODJWHY12A8C142CCE</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>509</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SODLLYS12A8C13A96B</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         user                song  play_count\n",
       "498  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOADQPP12A67020C82          12\n",
       "499  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOAFTRR12AF72A8D4D           1\n",
       "500  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOANQFY12AB0183239           1\n",
       "501  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOAYATB12A6701FD50           1\n",
       "502  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOBOAFP12A8C131F36           7\n",
       "503  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOBONKR12A58A7A7E0          26\n",
       "506  d6589314c0a9bcbca4fee0c93b14bc402363afea  SODASIJ12A6D4F5D89           1\n",
       "507  d6589314c0a9bcbca4fee0c93b14bc402363afea  SODEAWL12AB0187032           8\n",
       "508  d6589314c0a9bcbca4fee0c93b14bc402363afea  SODJWHY12A8C142CCE           8\n",
       "509  d6589314c0a9bcbca4fee0c93b14bc402363afea  SODLLYS12A8C13A96B           4"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "triplet_dataset_sub_song.head(n=10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 将歌曲元数据与用户播放记录合并"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 歌曲元数据\n",
    "Get the data from http://labrosa.ee.columbia.edu/millionsong/sites/default/files/AdditionalFiles/track_metadata.db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T13:48:12.545636Z",
     "start_time": "2017-09-24T13:48:12.536631Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('songs',)]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn = sqlite3.connect('track_metadata.db')\n",
    "cur = conn.cursor()\n",
    "cur.execute(\"SELECT name FROM sqlite_master WHERE type='table'\")\n",
    "cur.fetchall()  #数据表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T13:59:00.464363Z",
     "start_time": "2017-09-24T13:58:47.127394Z"
    }
   },
   "outputs": [],
   "source": [
    "track_metadata_df = pd.read_sql(con=conn, sql='select * from songs')\n",
    "\n",
    "#只保留流行歌曲（3万首）的元数据\n",
    "track_metadata_df_sub = track_metadata_df[track_metadata_df.song_id.isin(song_subset)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>track_id</th>\n",
       "      <th>title</th>\n",
       "      <th>song_id</th>\n",
       "      <th>release</th>\n",
       "      <th>artist_id</th>\n",
       "      <th>artist_mbid</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>duration</th>\n",
       "      <th>artist_familiarity</th>\n",
       "      <th>artist_hotttnesss</th>\n",
       "      <th>year</th>\n",
       "      <th>track_7digitalid</th>\n",
       "      <th>shs_perf</th>\n",
       "      <th>shs_work</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3812</th>\n",
       "      <td>TRMGUWH128F146903A</td>\n",
       "      <td>Sincerité Et Jalousie</td>\n",
       "      <td>SOBOUPA12A6D4F81F1</td>\n",
       "      <td>Simple Et Funky</td>\n",
       "      <td>ARZO9UQ1187FB4D261</td>\n",
       "      <td>8bc9464e-5967-477e-a9c3-ad5639d24517</td>\n",
       "      <td>Alliance Ethnik</td>\n",
       "      <td>195.94404</td>\n",
       "      <td>0.509271</td>\n",
       "      <td>0.437972</td>\n",
       "      <td>0</td>\n",
       "      <td>314771</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4635</th>\n",
       "      <td>TRMHHRK128F932A818</td>\n",
       "      <td>All Men Are Liars</td>\n",
       "      <td>SOLPVAQ12AB017EB35</td>\n",
       "      <td>Quiet Please... The New Best Of Nick Lowe</td>\n",
       "      <td>ARNJ2C31187B9B3239</td>\n",
       "      <td>cd09b0d0-1994-46ba-88bb-47d79f054396</td>\n",
       "      <td>Nick Lowe</td>\n",
       "      <td>203.75465</td>\n",
       "      <td>0.634281</td>\n",
       "      <td>0.436610</td>\n",
       "      <td>0</td>\n",
       "      <td>4623393</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5065</th>\n",
       "      <td>TRMHIPC128F42481F2</td>\n",
       "      <td>Sound Of Madness (Album Version)</td>\n",
       "      <td>SOLLWCK12A6D4FB0B9</td>\n",
       "      <td>The Sound of Madness</td>\n",
       "      <td>ARGCBAJ1187FB3F481</td>\n",
       "      <td>adc0f033-95c2-4e0b-87bc-c23ed3f26ce6</td>\n",
       "      <td>Shinedown</td>\n",
       "      <td>233.97832</td>\n",
       "      <td>0.796817</td>\n",
       "      <td>0.675192</td>\n",
       "      <td>2008</td>\n",
       "      <td>3022029</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5998</th>\n",
       "      <td>TRMCMYP128F145BC3D</td>\n",
       "      <td>The Maestro</td>\n",
       "      <td>SOMMONH12A6D4F41CD</td>\n",
       "      <td>Check Your Head</td>\n",
       "      <td>ARLHO5Z1187FB4C861</td>\n",
       "      <td>9beb62b2-88db-4cea-801e-162cd344ee53</td>\n",
       "      <td>Beastie Boys</td>\n",
       "      <td>172.22485</td>\n",
       "      <td>0.853125</td>\n",
       "      <td>0.591628</td>\n",
       "      <td>1992</td>\n",
       "      <td>253738</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6871</th>\n",
       "      <td>TRMCUTS128F1495E17</td>\n",
       "      <td>Kiss (LP Version)</td>\n",
       "      <td>SOESZWS12A6D4F910C</td>\n",
       "      <td>The Hits/The B-Sides 3</td>\n",
       "      <td>ARJ3CTF1187B9A1F2E</td>\n",
       "      <td>4c8ead39-b9df-4c56-a27c-51bc049cfd48</td>\n",
       "      <td>Prince &amp; The Revolution</td>\n",
       "      <td>227.65669</td>\n",
       "      <td>0.831929</td>\n",
       "      <td>0.485445</td>\n",
       "      <td>0</td>\n",
       "      <td>560524</td>\n",
       "      <td>9116</td>\n",
       "      <td>9116</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                track_id                             title  \\\n",
       "3812  TRMGUWH128F146903A             Sincerité Et Jalousie   \n",
       "4635  TRMHHRK128F932A818                 All Men Are Liars   \n",
       "5065  TRMHIPC128F42481F2  Sound Of Madness (Album Version)   \n",
       "5998  TRMCMYP128F145BC3D                       The Maestro   \n",
       "6871  TRMCUTS128F1495E17                 Kiss (LP Version)   \n",
       "\n",
       "                 song_id                                    release  \\\n",
       "3812  SOBOUPA12A6D4F81F1                            Simple Et Funky   \n",
       "4635  SOLPVAQ12AB017EB35  Quiet Please... The New Best Of Nick Lowe   \n",
       "5065  SOLLWCK12A6D4FB0B9                       The Sound of Madness   \n",
       "5998  SOMMONH12A6D4F41CD                            Check Your Head   \n",
       "6871  SOESZWS12A6D4F910C                     The Hits/The B-Sides 3   \n",
       "\n",
       "               artist_id                           artist_mbid  \\\n",
       "3812  ARZO9UQ1187FB4D261  8bc9464e-5967-477e-a9c3-ad5639d24517   \n",
       "4635  ARNJ2C31187B9B3239  cd09b0d0-1994-46ba-88bb-47d79f054396   \n",
       "5065  ARGCBAJ1187FB3F481  adc0f033-95c2-4e0b-87bc-c23ed3f26ce6   \n",
       "5998  ARLHO5Z1187FB4C861  9beb62b2-88db-4cea-801e-162cd344ee53   \n",
       "6871  ARJ3CTF1187B9A1F2E  4c8ead39-b9df-4c56-a27c-51bc049cfd48   \n",
       "\n",
       "                  artist_name   duration  artist_familiarity  \\\n",
       "3812          Alliance Ethnik  195.94404            0.509271   \n",
       "4635                Nick Lowe  203.75465            0.634281   \n",
       "5065                Shinedown  233.97832            0.796817   \n",
       "5998             Beastie Boys  172.22485            0.853125   \n",
       "6871  Prince & The Revolution  227.65669            0.831929   \n",
       "\n",
       "      artist_hotttnesss  year  track_7digitalid  shs_perf  shs_work  \n",
       "3812           0.437972     0            314771        -1         0  \n",
       "4635           0.436610     0           4623393        -1         0  \n",
       "5065           0.675192  2008           3022029        -1         0  \n",
       "5998           0.591628  1992            253738        -1         0  \n",
       "6871           0.485445     0            560524      9116      9116  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "track_metadata_df_sub.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "#python3.6不需要\n",
    "# -*- coding:utf-8 -*-\n",
    "#import sys\n",
    "#reload(sys)\n",
    "#sys.setdefaultencoding(\"utf-8\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T14:00:17.841834Z",
     "start_time": "2017-09-24T14:00:17.450258Z"
    }
   },
   "outputs": [],
   "source": [
    "track_metadata_df_sub.to_csv(path_or_buf ='track_metadata_df_sub.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T14:00:26.155770Z",
     "start_time": "2017-09-24T14:00:26.149763Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(844, 14)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "track_metadata_df_sub.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load up the saved data subsets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-26T05:35:13.855787Z",
     "start_time": "2017-09-26T05:35:00.267829Z"
    }
   },
   "outputs": [],
   "source": [
    "triplet_dataset_sub_song = pd.read_csv(filepath_or_buffer = 'triplet_dataset_sub_song.csv')\n",
    "track_metadata_df_sub = pd.read_csv(filepath_or_buffer = 'track_metadata_df_sub.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据清洗"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-26T05:35:26.643464Z",
     "start_time": "2017-09-26T05:35:14.956760Z"
    }
   },
   "outputs": [],
   "source": [
    "del(track_metadata_df_sub['track_id'])\n",
    "del(track_metadata_df_sub['artist_mbid'])\n",
    "\n",
    "#去除重复记录\n",
    "track_metadata_df_sub = track_metadata_df_sub.drop_duplicates(['song_id'])\n",
    "\n",
    "#播放历史和歌曲元数据合并\n",
    "triplet_dataset_sub_song_merged = pd.merge(triplet_dataset_sub_song, track_metadata_df_sub, how='left', left_on='song', right_on='song_id')\n",
    "triplet_dataset_sub_song_merged.rename(columns={'play_count':'listen_count'},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-26T05:35:31.415206Z",
     "start_time": "2017-09-26T05:35:29.271749Z"
    }
   },
   "outputs": [],
   "source": [
    "#去掉一些不用的列\n",
    "del(triplet_dataset_sub_song_merged['song_id'])\n",
    "del(triplet_dataset_sub_song_merged['artist_id'])\n",
    "del(triplet_dataset_sub_song_merged['duration'])\n",
    "del(triplet_dataset_sub_song_merged['artist_familiarity'])\n",
    "del(triplet_dataset_sub_song_merged['artist_hotttnesss'])\n",
    "del(triplet_dataset_sub_song_merged['track_7digitalid'])\n",
    "del(triplet_dataset_sub_song_merged['shs_perf'])\n",
    "del(triplet_dataset_sub_song_merged['shs_work'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T14:01:44.587532Z",
     "start_time": "2017-09-24T14:01:44.561014Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user</th>\n",
       "      <th>song</th>\n",
       "      <th>listen_count</th>\n",
       "      <th>title</th>\n",
       "      <th>release</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOADQPP12A67020C82</td>\n",
       "      <td>12</td>\n",
       "      <td>You And Me Jesus</td>\n",
       "      <td>Tribute To Jake Hess</td>\n",
       "      <td>Jake Hess</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOAFTRR12AF72A8D4D</td>\n",
       "      <td>1</td>\n",
       "      <td>Harder Better Faster Stronger</td>\n",
       "      <td>Discovery</td>\n",
       "      <td>Daft Punk</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOANQFY12AB0183239</td>\n",
       "      <td>1</td>\n",
       "      <td>Uprising</td>\n",
       "      <td>Uprising</td>\n",
       "      <td>Muse</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOAYATB12A6701FD50</td>\n",
       "      <td>1</td>\n",
       "      <td>Breakfast At Tiffany's</td>\n",
       "      <td>Home</td>\n",
       "      <td>Deep Blue Something</td>\n",
       "      <td>1993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOBOAFP12A8C131F36</td>\n",
       "      <td>7</td>\n",
       "      <td>Lucky (Album Version)</td>\n",
       "      <td>We Sing.  We Dance.  We Steal Things.</td>\n",
       "      <td>Jason Mraz &amp; Colbie Caillat</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOBONKR12A58A7A7E0</td>\n",
       "      <td>26</td>\n",
       "      <td>You're The One</td>\n",
       "      <td>If There Was A Way</td>\n",
       "      <td>Dwight Yoakam</td>\n",
       "      <td>1990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SODASIJ12A6D4F5D89</td>\n",
       "      <td>1</td>\n",
       "      <td>The Invisible Man</td>\n",
       "      <td>The Invisible Man</td>\n",
       "      <td>Michael Cretu</td>\n",
       "      <td>1985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SODEAWL12AB0187032</td>\n",
       "      <td>8</td>\n",
       "      <td>American Idiot [feat. Green Day &amp; The Cast Of ...</td>\n",
       "      <td>The Original Broadway Cast Recording 'American...</td>\n",
       "      <td>Green Day</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SODJWHY12A8C142CCE</td>\n",
       "      <td>8</td>\n",
       "      <td>Hey_ Soul Sister</td>\n",
       "      <td>Save Me_ San Francisco</td>\n",
       "      <td>Train</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SODLLYS12A8C13A96B</td>\n",
       "      <td>4</td>\n",
       "      <td>Breakeven</td>\n",
       "      <td>Now That's What I Call Music! 72</td>\n",
       "      <td>The Script</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       user                song  listen_count  \\\n",
       "0  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOADQPP12A67020C82            12   \n",
       "1  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOAFTRR12AF72A8D4D             1   \n",
       "2  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOANQFY12AB0183239             1   \n",
       "3  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOAYATB12A6701FD50             1   \n",
       "4  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOBOAFP12A8C131F36             7   \n",
       "5  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOBONKR12A58A7A7E0            26   \n",
       "6  d6589314c0a9bcbca4fee0c93b14bc402363afea  SODASIJ12A6D4F5D89             1   \n",
       "7  d6589314c0a9bcbca4fee0c93b14bc402363afea  SODEAWL12AB0187032             8   \n",
       "8  d6589314c0a9bcbca4fee0c93b14bc402363afea  SODJWHY12A8C142CCE             8   \n",
       "9  d6589314c0a9bcbca4fee0c93b14bc402363afea  SODLLYS12A8C13A96B             4   \n",
       "\n",
       "                                               title  \\\n",
       "0                                   You And Me Jesus   \n",
       "1                      Harder Better Faster Stronger   \n",
       "2                                           Uprising   \n",
       "3                             Breakfast At Tiffany's   \n",
       "4                              Lucky (Album Version)   \n",
       "5                                     You're The One   \n",
       "6                                  The Invisible Man   \n",
       "7  American Idiot [feat. Green Day & The Cast Of ...   \n",
       "8                                   Hey_ Soul Sister   \n",
       "9                                          Breakeven   \n",
       "\n",
       "                                             release  \\\n",
       "0                               Tribute To Jake Hess   \n",
       "1                                          Discovery   \n",
       "2                                           Uprising   \n",
       "3                                               Home   \n",
       "4              We Sing.  We Dance.  We Steal Things.   \n",
       "5                                 If There Was A Way   \n",
       "6                                  The Invisible Man   \n",
       "7  The Original Broadway Cast Recording 'American...   \n",
       "8                             Save Me_ San Francisco   \n",
       "9                   Now That's What I Call Music! 72   \n",
       "\n",
       "                   artist_name  year  \n",
       "0                    Jake Hess  2004  \n",
       "1                    Daft Punk  2007  \n",
       "2                         Muse     0  \n",
       "3          Deep Blue Something  1993  \n",
       "4  Jason Mraz & Colbie Caillat     0  \n",
       "5                Dwight Yoakam  1990  \n",
       "6                Michael Cretu  1985  \n",
       "7                    Green Day     0  \n",
       "8                        Train     0  \n",
       "9                   The Script  2008  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "triplet_dataset_sub_song_merged.head(n=10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 推荐系统使用的数据集\n",
    "triplet_dataset_sub_song_merged.to_csv(path_or_buf ='triplet_dataset_sub_song_merged.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 2297978 entries, 0 to 2297977\n",
      "Data columns (total 7 columns):\n",
      "user            object\n",
      "song            object\n",
      "listen_count    int64\n",
      "title           object\n",
      "release         object\n",
      "artist_name     object\n",
      "year            int64\n",
      "dtypes: int64(2), object(5)\n",
      "memory usage: 140.3+ MB\n"
     ]
    }
   ],
   "source": [
    "triplet_dataset_sub_song_merged.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  },
  "toc": {
   "nav_menu": {
    "height": "153px",
    "width": "160px"
   },
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "toc_cell": true,
   "toc_position": {
    "height": "691px",
    "left": "0px",
    "right": "1405px",
    "top": "106px",
    "width": "303px"
   },
   "toc_section_display": "block",
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
